package com.jmb.api.common.utils;



import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.JSONWriter;
import com.jmb.api.common.exception.WebApiException;
import com.jmb.api.common.result.ResultCodeEnum;
import com.jmb.api.common.result.SqlType;
import io.lettuce.core.BitFieldArgs;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;


import javax.sql.DataSource;
import java.io.File;
import java.io.StringReader;
import java.net.URL;
import java.sql.*;
import java.util.*;


@Slf4j
public class JdbcUtil {

    /**
     * 创建数据源
     *
     * @param url 数据库连接地址
     * @param username 数据库用户名
     * @param password 数据库密码
     * @param sqlType 数据库类型
     * @return 数据源
     */
    public static DruidDataSource getDruidDataSource(String url, String username, String password, SqlType sqlType) {
        DruidDataSource ds = null;
        try {
            HashMap<String, Object> map = new HashMap<>();
            map.put("init","false");
            map.put("url", url);
            map.put("username", username);
            map.put("password", password);
            DataSource dataSource = DruidDataSourceFactory.createDataSource(map);
            ds = (DruidDataSource) dataSource;
            // 配置数据源
            initDataSourceConfig(ds, sqlType);
            return ds;
        }catch (Exception e){
            log.error("创建数据源异常：{}", e.getMessage());
            if (ds!=null){
                try {
                    ds.close();
                }catch (Exception closeException){
                    log.error("关闭数据源异常：{}", closeException.getMessage());
                }
            }
            throw new WebApiException(ResultCodeEnum.SQL_ERROR);
        }
    }

    /**
     * 查询首行首列，带参数
     * @param url 数据库连接地址
     * @param username 数据库用户名
     * @param password 数据库密码
     * @param sql sql语句
     * @param params 参数 第一个参数为json path表达式，如果为空或者大小为0，那么返回首行首列结果
     * @return 查询结果
     */

    public static String selectFirst(String url, String username, String password, String sql,Object[] params){
        String resultStr = "";
        DruidDataSource druidDataSource = null;
        try {
           druidDataSource = getDruidDataSource(url, username, password, SqlType.SELECT);
            JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);
            if (params ==null || params.length==0){
                List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
                if (list.isEmpty()){
                    throw new SQLException("查询结果为空");
                }
                // 获取第一行数据
                Map<String, Object> fistRow = list.get(0);
                // 获取第一行数据第一列的值
                if (!fistRow.isEmpty()){
                    resultStr = fistRow.values().iterator().next().toString();
                }
            }else {
                String jsonPath = (String) params[0];
                Object[] remainingParams = Arrays.copyOfRange(params, 1, params.length);
                List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, remainingParams);
                String jsonString = JSON.toJSONString(list, JSONWriter.Feature.WriteMapNullValue);
                ArrayList<?> sqlResultArray = JSONObject.parseObject(ParseUtil.parseJson(jsonString, jsonPath), ArrayList.class);
                if (sqlResultArray.isEmpty()){
                    log.error("sql语句提取结果为空, sql={}, json path={}, params={}", sql, jsonPath, Arrays.toString(remainingParams));
                    throw new SQLException(String.format("sql语句提取结果为空, sql=%s, json path=%s, params=%s", sql, jsonPath, Arrays.toString(remainingParams)));
                }
                if (sqlResultArray.size() == 1){
                    Object o  = sqlResultArray.get(0);
                    if (o == null){
                        log.warn("sql语句提取结果为空，sql={}, json path={}, params={}", sql, jsonPath, Arrays.toString(remainingParams));
                        throw new SQLException(String.format("sql语句提取结果为空, sql=%s, json path=%s, params=%s", sql, jsonPath, Arrays.toString(remainingParams)));
                    }
                    return o.toString();
                }else {
                    log.error("sql语句提取结果有多行，sql={}, json path={}, params={}", sql, jsonPath, Arrays.toString(remainingParams));
                    return JSON.toJSONString(sqlResultArray);
                }
            }
        } catch (Exception e) {
            log.error("查询数据库异常：{}", e.getMessage());
            throw new WebApiException(ResultCodeEnum.SQL_ERROR);
        }finally {
            closeDataSource(druidDataSource);
        }
        return resultStr;
    }

    public static Long insert(String url, String username, String password, String sql, Object[] params) throws SQLException {
        KeyHolder holder = new GeneratedKeyHolder();
        DruidDataSource druidDataSource = null;
        PreparedStatementCreator preparedStatementCreator = con -> {
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    if (params [i] instanceof SqlParameterValue){
                        SqlParameterValue parameterValues = (SqlParameterValue) params[i];
                        StatementCreatorUtils.setParameterValue(ps, i + 1, parameterValues, parameterValues.getValue());
                    }else {
                        StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, params[i]);
                    }
                }
            }
            return ps;
        };
        try {
            druidDataSource = getDruidDataSource(url, username, password, SqlType.INSERT);
            JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);
            jdbcTemplate.update(preparedStatementCreator, holder);
            Number key = holder.getKey();
            if (key == null) {
                throw new SQLException("新增失败");
            }
            return key.longValue();
        } catch (Exception e) {
            log.error("JDBC TEMPLATE 新增失败", e);
            throw new SQLException("新增失败，请检查类型与SQL语句类型是否匹配" + e.getMessage());
        } finally {
            closeDataSource(druidDataSource);
        }
    }

    /**
     * 修改语句
     *
     * @param url      url
     * @param username username
     * @param password password
     * @param sql      sql
     * @param params   params
     * @return ""
     */
    public static String update(String url, String username, String password, String sql, Object[] params) throws SQLException {
        DruidDataSource druidDataSource = null;
        try {
            druidDataSource = getDruidDataSource(url, username, password, SqlType.UPDATE);
            JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);
            jdbcTemplate.update(sql, params);
        } catch (Exception e) {
            log.error("JDBC TEMPLATE 修改失败", e);
            throw new SQLException("修改失败，请检查类型与SQL语句类型是否匹配。" + e.getMessage(), e);
        } finally {
            closeDataSource(druidDataSource);
        }
        return "";
    }


    /**
     * 删除语句
     *
     * @param url      url
     * @param username username
     * @param password password
     * @param sql      sql
     * @param params   params
     * @return ""
     */
    public static String delete(String url, String username, String password, String sql, Object[] params) throws SQLException
    {
        DruidDataSource druidDataSource = null;
        try {
            druidDataSource = getDruidDataSource(url, username, password, SqlType.DELETE);
            JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);
            jdbcTemplate.update(sql, params);
        } catch (Exception e) {
            log.error("JDBC TEMPLATE 删除失败", e);
            throw new SQLException("删除失败，请检查类型与SQL语句类型是否匹配。" + e.getMessage(), e);
        } finally {
            closeDataSource(druidDataSource);
        }
        return "";
    }

    /**
     * 批量执行SQL脚本
     *
     * @param sql         sql
     * @param url         url
     * @param username    username
     * @param password    password
     * @param stopOnError stopOnError
     * @throws SQLException SqlException
     */
    public static String script(String sql, String url, String username, String password, boolean stopOnError) throws SQLException {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
            ScriptRunner runner = new ScriptRunner(conn);
            runner.setErrorLogWriter(null);
            runner.setLogWriter(null);
            runner.setStopOnError(stopOnError);
            runner.runScript(new StringReader(sql));
        } catch (SQLException e) {
            log.error("执行SQL脚本异常", e);
            throw new SQLException("执行SQL脚本异常", e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignored) {
                }
            }
        }
        return "";
    }


    // 检测数据库是否能连接通过
    public static boolean checkConnection(String url, String username, String password ) {
        try (DruidDataSource dataSource = new DruidDataSource()) {
            log.info("开始检测数据库连接");
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);

            log.info("数据源配置：{}", dataSource);
            // 初始化连接池
            dataSource.setInitialSize(1);
            // 最小空闲连接数
            dataSource.setMinIdle(1);
            // 最大连接数
            dataSource.setMaxActive(1);
            // 检测连接是否有效
            dataSource.setTestOnBorrow(true);
            // 检测连接是否有效的查询语句
            dataSource.setValidationQuery("SELECT 1");
            // 尝试获取连接
            Connection connection = dataSource.getConnection();
            if (connection != null && !connection.isClosed()) {
                connection.close();
                return true;
            }

        } catch (SQLException e) {
            log.error("数据库连接失败：{}", e.getMessage());
            return false;
        }
        return false;

    }


    /**
     * 执行sql脚本
     *
     */
    public static void runScript(String sql,String url,String username,String password,boolean stopOnError){
        log.info("开始执行sql脚本，url={}，username={}",url,password);
        try (Connection connection = DriverManager.getConnection(url,username,password)){
            ScriptRunner runner = new ScriptRunner(connection);
            runner.setErrorLogWriter( null);
            runner.setLogWriter( null);
            runner.setStopOnError(stopOnError);
            log.info("执行的Sql：{}",sql);
            runner.runScript(new StringReader(sql));
            log.info("执行Sql脚本成功，url={}，username={}",url,username);
        }catch (Exception e){
            log.error("执行SQL脚本异常, url={}, username={}, errorMsg={}", url, username, e.getMessage(), e);
            throw new WebApiException(ResultCodeEnum.web_SQl_ERROR);
        }
    }


    private static DruidDataSource initDataSourceConfig(DruidDataSource ds, SqlType sqlType) throws SQLException {
        if (sqlType == null){
            throw new WebApiException(ResultCodeEnum.web_SQl_TYPE_ERROR);
        }
        WallFilter wallFilter = new WallFilter();
        WallConfig wallConfig = new WallConfig();
        // 默认全部禁止
        wallConfig.setSelectAllow( false);
        wallConfig.setInsertAllow( false);
        wallConfig.setUpdateAllow( false);
        wallConfig.setDeleteAllow( false);
        wallConfig.setDropTableAllow( false);
        wallConfig.setAlterTableAllow(false);
        switch (sqlType) {
            case SELECT:
                wallConfig.setSelectAllow(true);
                break;
            case UPDATE:
                wallConfig.setUpdateAllow(true);
                break;
            case INSERT:
                wallConfig.setInsertAllow(true);
                break;
            case DELETE:
                wallConfig.setDeleteAllow(true);
                break;
            default:
                throw new WebApiException(ResultCodeEnum.web_SQl_TYPE_ERROR);
        }
        wallFilter.setConfig(wallConfig);
        List<Filter> proxyFilters = new ArrayList<>();
        proxyFilters.add(wallFilter);
        ds.setProxyFilters(proxyFilters);
        ds.setFailFast( true);
        ds.setConnectionErrorRetryAttempts(1);
        ds.setBreakAfterAcquireFailure( true);
        try {
            ds.init();
        } catch (SQLException e) {
            throw new SQLException("Failed to initialize Druid DataSource with security filters.", e);
        }
        return ds;
    }


    /**
     * 查询数据
     *
     * @param url 数据库连接地址
     * @param username 数据库用户名
     * @param password 数据库密码
     * @param sql 查询语句
     * @param <T> 查询结果类型
     * @return 查询结果
     */
    public static <T> List<T> queryForList(String url, String username, String password, String sql, Class<T> elementType) {
        DruidDataSource druidDataSource = null;
        JdbcTemplate jdbcTemplate;
        try {
            druidDataSource = getDruidDataSource(url, username, password, SqlType.SELECT);
            jdbcTemplate =  new JdbcTemplate(druidDataSource);
            return jdbcTemplate.queryForList(sql,elementType);
        }
        catch (Exception e) {
            log.error("执行SQL异常,url={},username={},password=***, errorMsg= ", url, username, e);
            throw new WebApiException(ResultCodeEnum.web_SQl_ERROR);
        } finally {
            if (druidDataSource != null) {
                druidDataSource.close();
            }
        }
    }
    // 私有方法用于统一关闭数据源
    private static void closeDataSource(DruidDataSource dataSource) {
        if (dataSource != null && !dataSource.isClosed()){
            try {
                dataSource.close();
            }catch (Exception e){
                log.error("关闭数据源异常", e);
            }
        }
    }
}
